package util;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;

public class PoiUtils {


    /**
     *第一行和字段名保持一致
     * @param datas
     * @param fields 写入的字段名
     * @param filePath
     * @param overwirte
     * @param <T>
     * @return
     */
    public static <T> boolean commonDataToExcel(List<T> datas, String[] fields, String filePath, boolean overwirte) {

        if (!filePath.endsWith(".xlsx")) {
            filePath = filePath + ".xlsx";
        }
      /*  File file = new File(filePath);
        if(!file.getParentFile().exists()){
            file.getParentFile().mkdirs();
        }*/

        boolean flag = false;
        //创建HSSFWorkbook对象(excel的文档对象)  
        XSSFWorkbook wb = new XSSFWorkbook();

        XSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        // 建立新的sheet对象（excel的表单）
        XSSFSheet sheet = wb.createSheet();

        for (int j = 0; j < datas.size(); j++) {
            Map map = objectToMap(datas.get(j));
            // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            XSSFRow row0 = sheet.createRow(0);

            for (int i = 0; i < fields.length; i++) {
                row0.createCell(i).setCellValue(fields[i]);
            }
            XSSFRow row = sheet.createRow(j + 1);
            for (int i = 0; i < fields.length; i++) {
                Object oldValue = map.get(fields[i]);
                String cellValue = null;
                if (oldValue != null && oldValue instanceof Date) {
                    cellValue =format((Date) oldValue, "yyyy-MM-dd HH:mm:ss");
                } else {
                    cellValue = nullToEmpty(oldValue);
                }

                if (cellValue.length() > 32767) {
                    cellValue = cellValue.substring(0, 32767);
                }
                row.createCell(i).setCellValue(cellValue);
            }
            System.out.println(j + "    to cell");
        }

        //输出Excel文件1  
        FileOutputStream output = null;
        try {
            if (overwirte) {
                new File(filePath).delete();
            }
            output = new FileOutputStream(filePath);
            wb.write(output);//写入磁盘  



            flag = true;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (output != null) {
                try {
                    output.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        System.out.println("data size is " + datas.size() + " to path===" + filePath);
        return flag;
    }




    public static List<Map<Character,Object>> readData(String path,char[] columns,boolean skipFirst) {
        InputStream inputStream = null;
        List<Map<Character,Object>> datas=new ArrayList<>();
        try {
            inputStream = new FileInputStream(path);
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                int rowNum = row.getRowNum();
                if (rowNum == 0 && skipFirst) {
                    continue;
                }
                for (char column : columns) {
                    Cell cell = row.getCell(getNum(column));

                    Object value = getCellValue(cell);
                    Map<Character,Object> map=new HashMap<>();
                    map.put(column,value);
                    datas.add(map);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if(inputStream!=null){
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return datas;
    }


    private static Object getCellValue(Cell cell) {
        Object value = null;
        CellType cellType = cell.getCellType();
        if(cell!=null){
            if(cellType.equals(CellType.STRING)){
                value = cell.getRichStringCellValue().getString();
            }else if(cellType.equals(CellType.NUMERIC)){
                if (DateUtil.isCellDateFormatted(cell)) { // 如果是date类型则 ，获取该cell的date值
                    value = cell.getDateCellValue();
//                    System.out.println(cell.getDateCellValue());
                } else {// 纯数字
                    value = cell.getNumericCellValue();
//                    System.out.println(cell.getNumericCellValue());
                }
            }else if(cellType.equals(CellType.BOOLEAN)){
                value = cell.getBooleanCellValue();
            } else if(cellType.equals(CellType.FORMULA)){
                value = cell.getCellFormula();
            } else if(cellType.equals(CellType.BLANK)){

            } else if(cellType.equals(CellType.ERROR)){

            }
        }


        return value;
    }

    private static Integer getNum(char ch) {
        return Integer.valueOf(ch) - 65;
    }




    private static String nullToEmpty(Object obj) {
        return obj == null ? "" : obj.toString();
    }


    /***
     * 将对象转换为map对象
     * @param thisObj 对象
     * @return
     */
    public static Map objectToMap(Object thisObj) {
        Map map = new HashMap();
        Class c;
        try {
            c = Class.forName(thisObj.getClass().getName());
            //获取所有的方法
            Method[] m = c.getMethods();
            for (int i = 0; i < m.length; i++) {   //获取方法名
                String method = m[i].getName();
                //获取get开始的方法名
                if (method.startsWith("get") && !method.contains("getClass")) {
                    try {
                        //获取对应对应get方法的value值
                        Object value = m[i].invoke(thisObj);
                        if (value != null) {
                            //截取get方法除get意外的字符 如getUserName-->UserName
                            String key = method.substring(3);
                            //将属性的第一个值转为小写
                            key = key.substring(0, 1).toLowerCase() + key.substring(1);
                            //将属性key,value放入对象
                            map.put(key, value);
                        }
                    } catch (Exception e) {
                        // TODO: handle exception
                        System.out.println("error:" + method);
                    }
                }
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        return map;
    }

    /**
     * 获取属性名数组
     */
    public static String[] getFiledName(Object o) {
        Field[] fields = o.getClass().getDeclaredFields();
        String[] fieldNames = new String[fields.length];
        for (int i = 0; i < fields.length; i++) {
            fieldNames[i] = fields[i].getName();
            System.out.println(fieldNames[i]);
        }
        return fieldNames;
    }


    private static String format(Date date, String format) {
        SimpleDateFormat sdf = new SimpleDateFormat(format);
        return sdf.format(date);
    }

    /**
     *自定义第一行
     * @param datas
     * @param fields 选中的字段
     * @param names 选中字段对应写入的名称
     * @param filePath
     * @param overwirte
     * @param <T>
     * @return
     */
    public static <T> boolean commonDataToExcel(List<T> datas, String[] fields, String[] names, String filePath, boolean overwirte) {

        if (!filePath.endsWith(".xlsx")) {
            filePath = filePath + ".xlsx";
        }
      /*  File file = new File(filePath);
        if(!file.getParentFile().exists()){
            file.getParentFile().mkdirs();
        }*/

        boolean flag = false;
        //创建HSSFWorkbook对象(excel的文档对象)  
        XSSFWorkbook wb = new XSSFWorkbook();

        XSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        // 建立新的sheet对象（excel的表单）
        XSSFSheet sheet = wb.createSheet();

        for (int j = 0; j < datas.size(); j++) {
            Map map = objectToMap(datas.get(j));
            // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            XSSFRow row0 = sheet.createRow(0);

            for (int i = 0; i < names.length; i++) {
                row0.createCell(i).setCellValue(names[i]);
            }
            XSSFRow row = sheet.createRow(j + 1);
            for (int i = 0; i < fields.length; i++) {
                Object oldValue = map.get(fields[i]);
                String cellValue = null;
                if (oldValue != null && oldValue instanceof Date) {
                    cellValue = format((Date) oldValue, "yyyy-MM-dd HH:mm:ss");
                } else {
                    cellValue = nullToEmpty(oldValue);
                }

                if (cellValue.length() > 32767) {
                    cellValue = cellValue.substring(0, 32767);
                }
                row.createCell(i).setCellValue(cellValue);
            }
            System.out.println(j + "    to cell");
        }

        //输出Excel文件1  
        FileOutputStream output = null;
        try {
            if (overwirte) {
                new File(filePath).delete();
            }
            output = new FileOutputStream(filePath);
            wb.write(output);//写入磁盘  
            flag = true;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (output != null) {
                try {
                    output.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        System.out.println("data size is " + datas.size() + " to path===" + filePath);
        return flag;
    }

}
